Excel BI - Excel Challenge 855

excel-challenges

excel-formulas

🔰 List teams who have won FIFA world cup non-consecutively and years of their non-consecutive winnings.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 855

Challenge Description

🔰 List teams who have won FIFA world cup non-consecutively and years of their non-consecutive winnings. If a country has ever won it consecutively, it should not be listed at all even if there are non-consecutive winnings also. Hence, Italy and Brazil are not listed. Sort it on Country name.

Solutions

library(tidyverse)
library(readxl)

path <- "Excel/800-899/855/855 Champion Non Continuous.xlsx"
input <- read_excel(path, range = "A2:B24")
test <- read_excel(path, range = "D2:E6")

result = input %>%
  mutate(n = n(), .by = Champion) %>%
  mutate(
    Consecutive = row_number() != 1 & lag(Champion) == Champion
  ) %>%
  filter(
    max(Consecutive) == FALSE,
    n >= 2,
    .by = Champion
  ) %>%
  summarise(
    Years = str_c(Year, collapse = ", "),
    .by = Champion
  ) %>%
  arrange(Champion)

all.equal(result, test, check.attributes = FALSE)
# [1] TRUE

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Aggregate or rank the data at the required grouping level.
Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.

import pandas as pd

path = "Excel/800-899/855/855 Champion Non Continuous.xlsx"
input = pd.read_excel(path, usecols="A:B", skiprows=1, nrows=23)
test = pd.read_excel(path, usecols="D:E", skiprows=1, nrows=4)

input['n'] = input.groupby('Champion')['Champion'].transform('size')
input['Consecutive'] = (input['Champion'] == input['Champion'].shift()) & (input.index != 0)
valid = (~input.groupby('Champion')['Consecutive'].transform('max')) & (input['n'] >= 2)
result = (
    input[valid]
    .groupby('Champion', as_index=False)
    .agg({'Year': lambda x: ', '.join(map(str, x))})
    .sort_values('Champion')
    .rename(columns={'Champion': 'Country', 'Year': 'Years'})
)

print(result.equals(test)) # True

The Python version follows the same grouped logic and keeps the transformation explicit in a dataframe pipeline.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.